package com.cencat.common.utils;

import lombok.experimental.UtilityClass;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;

import jakarta.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.stream.Collectors;

/**
 * Excel工具类
 * 提供Excel文件的读取、写入、导入导出、模板生成等功能
 * 支持.xls和.xlsx格式，提供丰富的数据转换和格式化功能
 * 
 * @author cencat
 * @since 2024-01-01
 */

@UtilityClass
public class ExcelUtils {

    private static final Logger log = LoggerFactory.getLogger(ExcelUtils.class);



    /**
     * Excel文件扩展名
     */
    private static final String EXCEL_XLS = ".xls";
    private static final String EXCEL_XLSX = ".xlsx";

    /**
     * 默认日期格式
     */
    private static final String DEFAULT_DATE_FORMAT = "yyyy-MM-dd HH:mm:ss";
    private static final DateTimeFormatter DATE_TIME_FORMATTER = DateTimeFormatter.ofPattern(DEFAULT_DATE_FORMAT);
    private static final SimpleDateFormat SIMPLE_DATE_FORMAT = new SimpleDateFormat(DEFAULT_DATE_FORMAT);

    /**
     * 最大行数限制
     */
    private static final int MAX_ROWS = 100000;
    
    /**
     * 最大列数限制
     */
    private static final int MAX_COLUMNS = 256;
    
    /**
     * 默认工作表名称
     */
    private static final String DEFAULT_SHEET_NAME = "Sheet1";
    
    /**
     * Excel内容类型
     */
    private static final String CONTENT_TYPE_XLS = "application/vnd.ms-excel";
    private static final String CONTENT_TYPE_XLSX = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    
    /**
     * 字符编码
     */
    private static final String CHARSET_UTF8 = "UTF-8";
    
    /**
     * 数字格式
     */
    private static final String NUMBER_FORMAT = "0.00";
    
    /**
     * 百分比格式
     */
    private static final String PERCENT_FORMAT = "0.00%";

    /**
     * 读取Excel文件
     * 
     * @param filePath 文件路径
     * @return Excel数据
     */
    public static ExcelData readExcel(String filePath) {
        if (CencatStringUtils.isBlank(filePath)) {
            throw new IllegalArgumentException("文件路径不能为空");
        }

        File file = new File(filePath);
        if (!file.exists()) {
            throw new IllegalArgumentException("文件不存在: " + filePath);
        }

        try (FileInputStream fis = new FileInputStream(file)) {
            return readExcel(fis, getFileExtension(filePath));
        } catch (IOException e) {
            log.error("读取Excel文件失败: {}", e.getMessage(), e);
            throw new RuntimeException("读取Excel文件失败", e);
        }
    }

    /**
     * 读取Excel文件
     * 
     * @param inputStream 输入流
     * @param extension 文件扩展名
     * @return Excel数据
     */
    public static ExcelData readExcel(InputStream inputStream, String extension) {
        if (inputStream == null) {
            throw new IllegalArgumentException("输入流不能为空");
        }

        try {
            Workbook workbook = createWorkbook(inputStream, extension);
            ExcelData excelData = new ExcelData();
            
            int numberOfSheets = workbook.getNumberOfSheets();
            for (int i = 0; i < numberOfSheets; i++) {
                Sheet sheet = workbook.getSheetAt(i);
                SheetData sheetData = readSheet(sheet);
                sheetData.setSheetName(sheet.getSheetName());
                sheetData.setSheetIndex(i);
                excelData.addSheet(sheetData);
            }
            
            workbook.close();
            return excelData;
            
        } catch (Exception e) {
            log.error("读取Excel数据失败: {}", e.getMessage(), e);
            throw new RuntimeException("读取Excel数据失败", e);
        }
    }

    /**
     * 读取Sheet数据
     * 
     * @param sheet Sheet对象
     * @return Sheet数据
     */
    private static SheetData readSheet(Sheet sheet) {
        SheetData sheetData = new SheetData();
        List<List<Object>> rows = new ArrayList<>();
        
        int lastRowNum = sheet.getLastRowNum();
        if (lastRowNum > MAX_ROWS) {
            log.warn("Excel行数超过限制，只读取前{}行", MAX_ROWS);
            lastRowNum = MAX_ROWS;
        }
        
        for (int i = 0; i <= lastRowNum; i++) {
            Row row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            
            List<Object> rowData = new ArrayList<>();
            int lastCellNum = row.getLastCellNum();
            
            for (int j = 0; j < lastCellNum; j++) {
                Cell cell = row.getCell(j);
                Object cellValue = getCellValue(cell);
                rowData.add(cellValue);
            }
            
            rows.add(rowData);
        }
        
        sheetData.setRows(rows);
        return sheetData;
    }

    /**
     * 获取单元格值
     * 
     * @param cell 单元格
     * @return 单元格值
     */
    private static Object getCellValue(Cell cell) {
        if (cell == null) {
            return null;
        }
        
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue();
                } else {
                    double numericValue = cell.getNumericCellValue();
                    // 判断是否为整数
                    if (numericValue == Math.floor(numericValue)) {
                        return (long) numericValue;
                    } else {
                        return numericValue;
                    }
                }
            case BOOLEAN:
                return cell.getBooleanCellValue();
            case FORMULA:
                try {
                    return cell.getNumericCellValue();
                } catch (Exception e) {
                    return cell.getStringCellValue();
                }
            case BLANK:
            case _NONE:
            default:
                return null;
        }
    }

    /**
     * 写入Excel文件
     * 
     * @param excelData Excel数据
     * @param filePath 文件路径
     */
    public static void writeExcel(ExcelData excelData, String filePath) {
        if (excelData == null || CencatStringUtils.isBlank(filePath)) {
            throw new IllegalArgumentException("参数不能为空");
        }

        try (FileOutputStream fos = new FileOutputStream(filePath)) {
            writeExcel(excelData, fos, getFileExtension(filePath));
        } catch (IOException e) {
            log.error("写入Excel文件失败: {}", e.getMessage(), e);
            throw new RuntimeException("写入Excel文件失败", e);
        }
    }

    /**
     * 写入Excel文件
     * 
     * @param excelData Excel数据
     * @param outputStream 输出流
     * @param extension 文件扩展名
     */
    public static void writeExcel(ExcelData excelData, OutputStream outputStream, String extension) {
        if (excelData == null || outputStream == null) {
            throw new IllegalArgumentException("参数不能为空");
        }

        try {
            Workbook workbook = createWorkbook(extension);
            
            List<SheetData> sheets = excelData.getSheets();
            for (SheetData sheetData : sheets) {
                Sheet sheet = workbook.createSheet(sheetData.getSheetName());
                writeSheet(sheet, sheetData);
            }
            
            workbook.write(outputStream);
            workbook.close();
            
        } catch (Exception e) {
            log.error("写入Excel数据失败: {}", e.getMessage(), e);
            throw new RuntimeException("写入Excel数据失败", e);
        }
    }

    /**
     * 写入Sheet数据
     * 
     * @param sheet Sheet对象
     * @param sheetData Sheet数据
     */
    private static void writeSheet(Sheet sheet, SheetData sheetData) {
        List<List<Object>> rows = sheetData.getRows();
        if (rows == null || rows.isEmpty()) {
            return;
        }
        
        for (int i = 0; i < rows.size(); i++) {
            Row row = sheet.createRow(i);
            List<Object> rowData = rows.get(i);
            
            for (int j = 0; j < rowData.size(); j++) {
                Cell cell = row.createCell(j);
                setCellValue(cell, rowData.get(j));
            }
        }
        
        // 自动调整列宽
        if (!rows.isEmpty()) {
            int columnCount = rows.get(0).size();
            for (int i = 0; i < columnCount; i++) {
                try {
                    sheet.autoSizeColumn(i);
                } catch (Exception e) {
                    // 忽略自动调整列宽的异常
                }
            }
        }
    }

    /**
     * 设置单元格值
     * 
     * @param cell 单元格
     * @param value 值
     */
    private static void setCellValue(Cell cell, Object value) {
        if (value == null) {
            cell.setCellValue("");
            return;
        }
        
        if (value instanceof String) {
            cell.setCellValue((String) value);
        } else if (value instanceof Number) {
            cell.setCellValue(((Number) value).doubleValue());
        } else if (value instanceof Boolean) {
            cell.setCellValue((Boolean) value);
        } else if (value instanceof Date) {
            cell.setCellValue((Date) value);
        } else if (value instanceof LocalDateTime) {
            cell.setCellValue(((LocalDateTime) value).format(DATE_TIME_FORMATTER));
        } else if (value instanceof LocalDate) {
            cell.setCellValue(((LocalDate) value).toString());
        } else {
            cell.setCellValue(value.toString());
        }
    }

    /**
     * 导出Excel到HTTP响应
     * 
     * @param excelData Excel数据
     * @param fileName 文件名
     * @param response HTTP响应
     */
    public static void exportExcel(ExcelData excelData, String fileName, HttpServletResponse response) {
        if (excelData == null || CencatStringUtils.isBlank(fileName) || response == null) {
            throw new IllegalArgumentException("参数不能为空");
        }

        try {
            // 设置响应头
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("UTF-8");
            
            String encodedFileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.name());
            response.setHeader("Content-Disposition", "attachment; filename=" + encodedFileName);
            
            // 写入Excel数据
            writeExcel(excelData, response.getOutputStream(), EXCEL_XLSX);
            
        } catch (Exception e) {
            log.error("导出Excel失败: {}", e.getMessage(), e);
            throw new RuntimeException("导出Excel失败", e);
        }
    }

    /**
     * 将对象列表转换为Excel数据
     * 
     * @param dataList 数据列表
     * @param clazz 对象类型
     * @param <T> 泛型类型
     * @return Excel数据
     */
    public static <T> ExcelData convertToExcelData(List<T> dataList, Class<T> clazz) {
        return convertToExcelData(dataList, clazz, "Sheet1");
    }

    /**
     * 将对象列表转换为Excel数据
     * 
     * @param dataList 数据列表
     * @param clazz 对象类型
     * @param sheetName Sheet名称
     * @param <T> 泛型类型
     * @return Excel数据
     */
    public static <T> ExcelData convertToExcelData(List<T> dataList, Class<T> clazz, String sheetName) {
        if (dataList == null || clazz == null) {
            throw new IllegalArgumentException("参数不能为空");
        }

        try {
            ExcelData excelData = new ExcelData();
            SheetData sheetData = new SheetData();
            sheetData.setSheetName(CencatStringUtils.isNotBlank(sheetName) ? sheetName : "Sheet1");
            
            List<List<Object>> rows = new ArrayList<>();
            
            // 获取字段信息
            Field[] fields = clazz.getDeclaredFields();
            List<Field> validFields = new ArrayList<>();
            
            for (Field field : fields) {
                field.setAccessible(true);
                validFields.add(field);
            }
            
            // 添加表头
            List<Object> headerRow = new ArrayList<>();
            for (Field field : validFields) {
                headerRow.add(field.getName());
            }
            rows.add(headerRow);
            
            // 添加数据行
            for (T item : dataList) {
                List<Object> dataRow = new ArrayList<>();
                for (Field field : validFields) {
                    Object value = field.get(item);
                    dataRow.add(value);
                }
                rows.add(dataRow);
            }
            
            sheetData.setRows(rows);
            excelData.addSheet(sheetData);
            
            return excelData;
            
        } catch (Exception e) {
            log.error("转换Excel数据失败: {}", e.getMessage(), e);
            throw new RuntimeException("转换Excel数据失败", e);
        }
    }

    /**
     * 将Excel数据转换为对象列表
     * 
     * @param excelData Excel数据
     * @param clazz 对象类型
     * @param <T> 泛型类型
     * @return 对象列表
     */
    public static <T> List<T> convertFromExcelData(ExcelData excelData, Class<T> clazz) {
        return convertFromExcelData(excelData, clazz, 0, true);
    }

    /**
     * 将Excel数据转换为对象列表
     * 
     * @param excelData Excel数据
     * @param clazz 对象类型
     * @param sheetIndex Sheet索引
     * @param hasHeader 是否包含表头
     * @param <T> 泛型类型
     * @return 对象列表
     */
    public static <T> List<T> convertFromExcelData(ExcelData excelData, Class<T> clazz, int sheetIndex, boolean hasHeader) {
        if (excelData == null || clazz == null) {
            throw new IllegalArgumentException("参数不能为空");
        }

        try {
            List<SheetData> sheets = excelData.getSheets();
            if (sheets.isEmpty() || sheetIndex >= sheets.size()) {
                return new ArrayList<>();
            }
            
            SheetData sheetData = sheets.get(sheetIndex);
            List<List<Object>> rows = sheetData.getRows();
            
            if (rows.isEmpty()) {
                return new ArrayList<>();
            }
            
            // 获取字段信息
            Field[] fields = clazz.getDeclaredFields();
            for (Field field : fields) {
                field.setAccessible(true);
            }
            
            List<T> resultList = new ArrayList<>();
            int startRow = hasHeader ? 1 : 0;
            
            for (int i = startRow; i < rows.size(); i++) {
                List<Object> row = rows.get(i);
                T instance = clazz.getDeclaredConstructor().newInstance();
                
                for (int j = 0; j < Math.min(row.size(), fields.length); j++) {
                    Object cellValue = row.get(j);
                    if (cellValue != null) {
                        Field field = fields[j];
                        setFieldValue(instance, field, cellValue);
                    }
                }
                
                resultList.add(instance);
            }
            
            return resultList;
            
        } catch (Exception e) {
            log.error("转换对象列表失败: {}", e.getMessage(), e);
            throw new RuntimeException("转换对象列表失败", e);
        }
    }

    /**
     * 设置字段值
     * 
     * @param instance 对象实例
     * @param field 字段
     * @param value 值
     */
    private static void setFieldValue(Object instance, Field field, Object value) {
        try {
            Class<?> fieldType = field.getType();
            Object convertedValue = convertValue(value, fieldType);
            field.set(instance, convertedValue);
        } catch (Exception e) {
            log.warn("设置字段值失败: field={}, value={}, error={}", field.getName(), value, e.getMessage());
        }
    }

    /**
     * 转换值类型
     * 
     * @param value 原始值
     * @param targetType 目标类型
     * @return 转换后的值
     */
    private static Object convertValue(Object value, Class<?> targetType) {
        if (value == null) {
            return null;
        }
        
        if (targetType.isAssignableFrom(value.getClass())) {
            return value;
        }
        
        String stringValue = value.toString();
        
        if (targetType == String.class) {
            return stringValue;
        } else if (targetType == Integer.class || targetType == int.class) {
            return Integer.valueOf(stringValue.split("\\.")[0]);
        } else if (targetType == Long.class || targetType == long.class) {
            return Long.valueOf(stringValue.split("\\.")[0]);
        } else if (targetType == Double.class || targetType == double.class) {
            return Double.valueOf(stringValue);
        } else if (targetType == Float.class || targetType == float.class) {
            return Float.valueOf(stringValue);
        } else if (targetType == BigDecimal.class) {
            return new BigDecimal(stringValue);
        } else if (targetType == Boolean.class || targetType == boolean.class) {
            return Boolean.valueOf(stringValue);
        } else if (targetType == Date.class) {
            if (value instanceof Date) {
                return value;
            } else {
                try {
                    return SIMPLE_DATE_FORMAT.parse(stringValue);
                } catch (Exception e) {
                    return null;
                }
            }
        }
        
        return value;
    }

    /**
     * 创建Workbook
     * 
     * @param extension 文件扩展名
     * @return Workbook对象
     */
    private static Workbook createWorkbook(String extension) {
        if (EXCEL_XLSX.equalsIgnoreCase(extension)) {
            return new XSSFWorkbook();
        } else {
            return new HSSFWorkbook();
        }
    }

    /**
     * 创建Workbook
     * 
     * @param inputStream 输入流
     * @param extension 文件扩展名
     * @return Workbook对象
     */
    private static Workbook createWorkbook(InputStream inputStream, String extension) throws IOException {
        if (EXCEL_XLSX.equalsIgnoreCase(extension)) {
            return new XSSFWorkbook(inputStream);
        } else {
            return new HSSFWorkbook(inputStream);
        }
    }

    /**
     * 获取文件扩展名
     * 
     * @param fileName 文件名
     * @return 扩展名
     */
    private static String getFileExtension(String fileName) {
        if (CencatStringUtils.isBlank(fileName)) {
            return EXCEL_XLSX;
        }
        
        int lastDotIndex = fileName.lastIndexOf('.');
        if (lastDotIndex == -1) {
            return EXCEL_XLSX;
        }
        
        return fileName.substring(lastDotIndex);
    }

    /**
     * 验证Excel文件格式
     * 
     * @param fileName 文件名
     * @return 是否为有效的Excel文件
     */
    public static boolean isValidExcelFile(String fileName) {
        if (CencatStringUtils.isBlank(fileName)) {
            return false;
        }
        
        String extension = getFileExtension(fileName).toLowerCase();
        return EXCEL_XLS.equals(extension) || EXCEL_XLSX.equals(extension);
    }

    /**
     * Excel数据封装类
     */
    public static class ExcelData {
        /**
         * Sheet列表
         */
        private List<SheetData> sheets = new ArrayList<>();

        /**
         * 添加Sheet
         */
        public void addSheet(SheetData sheetData) {
            if (sheetData != null) {
                this.sheets.add(sheetData);
            }
        }

        /**
         * 获取第一个Sheet
         */
        public SheetData getFirstSheet() {
            return sheets.isEmpty() ? null : sheets.get(0);
        }

        /**
         * 根据名称获取Sheet
         */
        public SheetData getSheetByName(String sheetName) {
            if (CencatStringUtils.isBlank(sheetName)) {
                return null;
            }
            
            return sheets.stream()
                    .filter(sheet -> sheetName.equals(sheet.getSheetName()))
                    .findFirst()
                    .orElse(null);
        }

        /**
         * 根据索引获取Sheet
         */
        public SheetData getSheetByIndex(int index) {
            if (index < 0 || index >= sheets.size()) {
                return null;
            }
            return sheets.get(index);
        }

        // Getter and Setter methods
        public List<SheetData> getSheets() {
            return sheets;
        }

        public void setSheets(List<SheetData> sheets) {
            this.sheets = sheets;
        }
    }

    /**
     * Sheet数据
     */
    public static class SheetData {
        /**
         * Sheet名称
         */
        private String sheetName;

        /**
         * Sheet索引
         */
        private int sheetIndex;

        /**
         * 行数据
         */
        private List<List<Object>> rows = new ArrayList<>();

        /**
         * 添加行
         */
        public void addRow(List<Object> row) {
            if (row != null) {
                this.rows.add(row);
            }
        }

        /**
         * 获取行数
         */
        public int getRowCount() {
            return rows.size();
        }

        /**
         * 获取列数
         */
        public int getColumnCount() {
            return rows.isEmpty() ? 0 : rows.get(0).size();
        }

        /**
         * 获取指定行的数据
         */
        public List<Object> getRow(int rowIndex) {
            if (rowIndex < 0 || rowIndex >= rows.size()) {
                return null;
            }
            return rows.get(rowIndex);
        }

        /**
         * 获取指定单元格的值
         */
        public Object getCellValue(int rowIndex, int columnIndex) {
            List<Object> row = getRow(rowIndex);
            if (row == null || columnIndex < 0 || columnIndex >= row.size()) {
                return null;
            }
            return row.get(columnIndex);
        }

        // Getter and Setter methods
        public String getSheetName() {
            return sheetName;
        }

        public void setSheetName(String sheetName) {
            this.sheetName = sheetName;
        }

        public int getSheetIndex() {
            return sheetIndex;
        }

        public void setSheetIndex(int sheetIndex) {
            this.sheetIndex = sheetIndex;
        }

        public List<List<Object>> getRows() {
            return rows;
        }

        public void setRows(List<List<Object>> rows) {
            this.rows = rows;
        }
    }
    
    /**
     * 导入结果
     */
    public static class ImportResult<T> {
        /**
         * 成功导入的数据
         */
        private List<T> successList = new ArrayList<>();
        
        /**
         * 导入失败的数据
         */
        private List<ImportError> errorList = new ArrayList<>();
        
        /**
         * 总行数
         */
        private int totalRows;
        
        /**
         * 成功行数
         */
        private int successRows;
        
        /**
         * 失败行数
         */
        private int errorRows;
        
        /**
         * 是否有错误
         */
        public boolean hasError() {
            return !errorList.isEmpty();
        }

        // Getter and Setter methods
        public List<T> getSuccessList() {
            return successList;
        }

        public void setSuccessList(List<T> successList) {
            this.successList = successList;
        }

        public List<ImportError> getErrorList() {
            return errorList;
        }

        public void setErrorList(List<ImportError> errorList) {
            this.errorList = errorList;
        }

        public int getTotalRows() {
            return totalRows;
        }

        public void setTotalRows(int totalRows) {
            this.totalRows = totalRows;
        }

        public int getSuccessRows() {
            return successRows;
        }

        public void setSuccessRows(int successRows) {
            this.successRows = successRows;
        }

        public int getErrorRows() {
            return errorRows;
        }

        public void setErrorRows(int errorRows) {
            this.errorRows = errorRows;
        }
    }
    
    /**
     * 导入错误信息
     */
    public static class ImportError {
        /**
         * 行号
         */
        private int rowIndex;
        
        /**
         * 错误信息
         */
        private String errorMessage;
        
        /**
         * 原始数据
         */
        private List<Object> rowData;
        
        public ImportError(int rowIndex, String errorMessage, List<Object> rowData) {
            this.rowIndex = rowIndex;
            this.errorMessage = errorMessage;
            this.rowData = rowData;
        }

        // Getter and Setter methods
        public int getRowIndex() {
            return rowIndex;
        }

        public void setRowIndex(int rowIndex) {
            this.rowIndex = rowIndex;
        }

        public String getErrorMessage() {
            return errorMessage;
        }

        public void setErrorMessage(String errorMessage) {
            this.errorMessage = errorMessage;
        }

        public List<Object> getRowData() {
            return rowData;
        }

        public void setRowData(List<Object> rowData) {
            this.rowData = rowData;
        }
    }
    
    /**
     * Excel常量类
     */
    public static class ExcelConstants {
        /**
         * 文件扩展名
         */
        public static final String XLS = ".xls";
        public static final String XLSX = ".xlsx";
        
        /**
         * 内容类型
         */
        public static final String CONTENT_TYPE_XLS = "application/vnd.ms-excel";
        public static final String CONTENT_TYPE_XLSX = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        
        /**
         * 默认配置
         */
        public static final String DEFAULT_SHEET_NAME = "Sheet1";
        public static final String DEFAULT_DATE_FORMAT = "yyyy-MM-dd HH:mm:ss";
        public static final int DEFAULT_HEADER_ROW = 0;
        public static final int MAX_ROWS = 100000;
        public static final int MAX_COLUMNS = 256;
        
        private ExcelConstants() {
            super();
        }
    }
    
    // ==================== 扩展功能方法 ====================
    
    /**
     * 创建Excel模板
     * 
     * @param headers 表头列表
     * @param sheetName 工作表名称
     * @param extension 文件扩展名
     * @return Excel工作簿
     */
    public static Workbook createTemplate(List<String> headers, String sheetName, String extension) {
        if (headers == null || headers.isEmpty()) {
            throw new IllegalArgumentException("表头不能为空");
        }
        
        Workbook workbook = createWorkbook(extension);
        Sheet sheet = workbook.createSheet(CencatStringUtils.isBlank(sheetName) ? DEFAULT_SHEET_NAME : sheetName);
        
        // 创建表头样式
        CellStyle headerStyle = createHeaderStyle(workbook);
        
        // 创建表头行
        Row headerRow = sheet.createRow(0);
        for (int i = 0; i < headers.size(); i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headers.get(i));
            cell.setCellStyle(headerStyle);
            
            // 自动调整列宽
            sheet.autoSizeColumn(i);
        }
        
        return workbook;
    }
    
    /**
     * 创建表头样式
     * 
     * @param workbook 工作簿
     * @return 表头样式
     */
    private static CellStyle createHeaderStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        
        // 设置背景色
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        
        // 设置边框
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        
        // 设置字体
        Font font = workbook.createFont();
        font.setBold(true);
        font.setFontHeightInPoints((short) 12);
        style.setFont(font);
        
        // 设置对齐方式
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        
        return style;
    }
    
    /**
     * 批量导入Excel数据
     * 
     * @param <T> 数据类型
     * @param inputStream 输入流
     * @param extension 文件扩展名
     * @param clazz 目标类
     * @param hasHeader 是否包含表头
     * @return 导入结果
     */
    public static <T> ImportResult<T> batchImport(InputStream inputStream, String extension, 
                                                  Class<T> clazz, boolean hasHeader) {
        ImportResult<T> result = new ImportResult<>();
        
        try {
            ExcelData excelData = readExcel(inputStream, extension);
            if (excelData.getSheets().isEmpty()) {
                return result;
            }
            
            SheetData sheetData = excelData.getFirstSheet();
            List<List<Object>> rows = sheetData.getRows();
            
            result.setTotalRows(rows.size() - (hasHeader ? 1 : 0));
            
            int startRow = hasHeader ? 1 : 0;
            for (int i = startRow; i < rows.size(); i++) {
                List<Object> row = rows.get(i);
                try {
                    T instance = convertRowToObject(row, clazz);
                    result.getSuccessList().add(instance);
                    result.setSuccessRows(result.getSuccessRows() + 1);
                } catch (Exception e) {
                    ImportError error = new ImportError(i + 1, e.getMessage(), row);
                    result.getErrorList().add(error);
                    result.setErrorRows(result.getErrorRows() + 1);
                }
            }
            
        } catch (Exception e) {
            log.error("批量导入Excel失败: {}", e.getMessage(), e);
            throw new RuntimeException("批量导入Excel失败", e);
        }
        
        return result;
    }
    
    /**
     * 将行数据转换为对象
     * 
     * @param <T> 对象类型
     * @param row 行数据
     * @param clazz 目标类
     * @return 转换后的对象
     */
    private static <T> T convertRowToObject(List<Object> row, Class<T> clazz) throws Exception {
        T instance = clazz.getDeclaredConstructor().newInstance();
        Field[] fields = clazz.getDeclaredFields();
        
        for (int i = 0; i < Math.min(row.size(), fields.length); i++) {
            Field field = fields[i];
            field.setAccessible(true);
            
            Object value = row.get(i);
            if (value != null) {
                Object convertedValue = convertValue(value, field.getType());
                field.set(instance, convertedValue);
            }
        }
        
        return instance;
    }
    
    /**
     * 验证Excel文件格式
     * 
     * @param fileName 文件名
     * @param maxSize 最大文件大小（字节）
     * @return 验证结果
     */
    public static ValidationResult validateExcelFile(String fileName, long maxSize) {
        ValidationResult result = new ValidationResult();
        
        // 检查文件名
        if (CencatStringUtils.isBlank(fileName)) {
            result.addError("文件名不能为空");
            return result;
        }
        
        // 检查文件扩展名
        if (!isValidExcelFile(fileName)) {
            result.addError("不支持的文件格式，仅支持.xls和.xlsx格式");
        }
        
        // 检查文件大小
        File file = new File(fileName);
        if (file.exists() && file.length() > maxSize) {
            result.addError(String.format("文件大小超过限制，最大允许%d字节", maxSize));
        }
        
        return result;
    }
    
    /**
     * 验证结果类
     */
    public static class ValidationResult {
        private boolean valid = true;
        private List<String> errors = new ArrayList<>();
        
        public void addError(String error) {
            this.valid = false;
            this.errors.add(error);
        }
        
        public String getErrorMessage() {
            return String.join("; ", errors);
        }

        // Getter and Setter methods
        public boolean isValid() {
            return valid;
        }

        public void setValid(boolean valid) {
            this.valid = valid;
        }

        public List<String> getErrors() {
            return errors;
        }

        public void setErrors(List<String> errors) {
            this.errors = errors;
        }
    }
    
    /**
     * 获取Excel文件信息
     * 
     * @param filePath 文件路径
     * @return Excel文件信息
     */
    public static ExcelFileInfo getExcelFileInfo(String filePath) {
        ExcelFileInfo info = new ExcelFileInfo();
        
        try (FileInputStream fis = new FileInputStream(filePath)) {
            String extension = getFileExtension(filePath);
            Workbook workbook = createWorkbook(fis, extension);
            
            info.setFileName(new File(filePath).getName());
            info.setFileSize(new File(filePath).length());
            info.setSheetCount(workbook.getNumberOfSheets());
            
            List<String> sheetNames = new ArrayList<>();
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                sheetNames.add(workbook.getSheetAt(i).getSheetName());
            }
            info.setSheetNames(sheetNames);
            
            workbook.close();
            
        } catch (Exception e) {
            log.error("获取Excel文件信息失败: {}", e.getMessage(), e);
            throw new RuntimeException("获取Excel文件信息失败", e);
        }
        
        return info;
    }
    
    /**
     * Excel文件信息类
     */
    public static class ExcelFileInfo {
        private String fileName;
        private long fileSize;
        private int sheetCount;
        private List<String> sheetNames;

        // Getter and Setter methods
        public String getFileName() {
            return fileName;
        }

        public void setFileName(String fileName) {
            this.fileName = fileName;
        }

        public long getFileSize() {
            return fileSize;
        }

        public void setFileSize(long fileSize) {
            this.fileSize = fileSize;
        }

        public int getSheetCount() {
            return sheetCount;
        }

        public void setSheetCount(int sheetCount) {
            this.sheetCount = sheetCount;
        }

        public List<String> getSheetNames() {
            return sheetNames;
        }

        public void setSheetNames(List<String> sheetNames) {
            this.sheetNames = sheetNames;
        }
    }
}